package sanrenxing.foreMain.dao.impl;

import sanrenxing.foreMain.dao.NoticeShowDao;
import sanrenxing.foreMain.domain.NoticeShow;
import sanrenxing.foreMain.servlet.NoticeShowServlet;
import sanrenxing.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Administrator
 * @date 2020/10/14 22:40
 * 编辑者：邓志强
 * 功能：
 */
public class NoticeShowDaoImpl implements NoticeShowDao {
    /**
     * 查询公告方法
     *
     * @param stuId
     * @return
     */
    @Override
    public List<NoticeShow> findNotice(String stuId) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //创建数组
        List<NoticeShow> list=new ArrayList<>();
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写要执行的sql命令语句
            String sql="SELECT noticeId,teacherId,noticeBody,classId,noticeBeginTime,noticeOutTime FROM" +
                    " notice WHERE classId=(SELECT classId FROM student WHERE stuId=?)";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //给sql命令语句中的占位符赋值
            pstmt.setString(1,stuId);
            //执行预编译语句并返回结果集
            rs=pstmt.executeQuery();
            //对结果集进行循环处理
            while (rs.next()){
                //创建公告对象
                NoticeShow noticeShow =new NoticeShow();
                //把结果集中一行数据保存到公告对象中
                noticeShow.setNoticeId(rs.getInt("noticeId"));
                noticeShow.setTeacherId(rs.getInt("teacherId"));
                noticeShow.setNoticeBody(rs.getString("noticeBody"));
                noticeShow.setClassId(rs.getInt("classId"));
                noticeShow.setNoticeBeginTime(rs.getString("noticeBeginTime"));
                noticeShow.setNoticeOutTime(rs.getString("noticeOutTime"));
                //把数据添加到集合中
                list.add(noticeShow);
            }
        } catch (SQLException e) {
            //运行时异常
            new RuntimeException(e.getMessage(),e);
        }finally{
            //释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回集合
        return list;
    }
}
